#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive	

${HIVE_HOME} -S -e "


--建库
CREATE DATABASE if not exists online_edu_rpt;

--需求1:每天/每月/每年各个校区的报名人数
--先删表
DROP TABLE online_edu_rpt.rpt_school_apply_cnt;
--再建表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_school_apply_cnt(
     --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    itcast_school_id INT COMMENT '校区ID',
    itcast_school_name string COMMENT '校区名称',
    --指标
    apply_cnt bigint COMMENT '报名人数'
)
COMMENT '每天/每月/每年各个校区的报名人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

------------------------------------------------------------------
-- 需求2:每天/每月/每年线上线下各个校区的报名人数
--先删表
DROP TABLE online_edu_rpt.rpt_type_school_apply_cnt;
--再创表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_type_school_apply_cnt(
    --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    origin_type_new string COMMENT '线上线下',
    itcast_school_id INT COMMENT '校区ID',
    itcast_school_name string COMMENT '校区名称',
    --指标
    apply_cnt bigint COMMENT '报名人数'
)
COMMENT '每天/每月/每年线上线下各个校区的报名人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

------------------------------------------------------------------
-- 需求3:每天/每月/每年线上线下各个学科的报名人数
--先删表
DROP TABLE online_edu_rpt.rpt_type_subject_apply_cnt;
--再建表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_type_subject_apply_cnt(
    --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    origin_type_new string COMMENT '线上线下',
    itcast_subject_id INT COMMENT '学科ID',
    itcast_subject_name string COMMENT '学科名称',
    --指标
    apply_cnt bigint COMMENT '报名人数'
)
COMMENT '每天/每月/每年线上线下各个学科的报名人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

------------------------------------------------------------------
-- 需求4:每天/每月/每年线上线下各个校区各个学科的报名人数
--先删表
DROP TABLE online_edu_rpt.rpt_type_school_subject_apply_cnt;
--再建表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_type_school_subject_apply_cnt(
    --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    origin_type_new string COMMENT '线上线下',
    itcast_school_id INT COMMENT '校区ID',
    itcast_school_name string COMMENT '校区名称',
    itcast_subject_id INT COMMENT '学科ID',
    itcast_subject_name string COMMENT '学科名称',
    --指标
    apply_cnt bigint COMMENT '报名人数'
)
COMMENT '每天/每月/每年线上线下各个校区各个学科的报名人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

------------------------------------------------------------------
-- 需求5:每天/每月/每年线上线下各个来源渠道的报名人数
--先删表
DROP TABLE online_edu_rpt.rpt_type_channel_apply_cnt;
--再建表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_type_channel_apply_cnt(
    --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    origin_type_new string COMMENT '线上线下',
    origin_channel string COMMENT '来源渠道',
    --指标
    apply_cnt bigint COMMENT '报名人数'
)
COMMENT '每天/每月/每年线上线下各个来源渠道的报名人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

------------------------------------------------------------------
-- 需求6:每天/每月/每年线上线下各个咨询中心的报名人数
--先删表
DROP TABLE online_edu_rpt.rpt_type_tdepart_apply_cnt;
--再建表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_type_tdepart_apply_cnt(
    --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    origin_type_new string COMMENT '线上线下',
    tdepart_id INT COMMENT '咨询中心部门id',
    tdepart_name string COMMENT '咨询中心部门名称',
    --指标
    apply_cnt bigint COMMENT '报名人数'
)
COMMENT '每天/每月/每年线上线下各个咨询中心的报名人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

------------------------------------------------------------------
-- 需求7:每天/每月/每年线上线下的意向转报名率 = 报名人数 / 意向人数
--先删表
DROP TABLE online_edu_rpt.rpt_intention_apply_conversion_rate;
--再建表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_intention_apply_conversion_rate(
    --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    origin_type_new string COMMENT '线上线下',
    --指标
    apply_cnt bigint COMMENT '报名人数',
    intentional_cnt bigint COMMENT '意向人数',
    apply_conversion_rate DECIMAL(5,2) COMMENT '意向转报名率'
)
COMMENT '每天/每月/每年线上线下的意向转报名率 = 报名人数 / 意向人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

------------------------------------------------------------------
-- 需求8:每天/每月/每年线上的有效线索报名转换率 = 报名人数 / 有效线索人数
--先删表
DROP TABLE online_edu_rpt.rpt_valid_clue_apply_conversion_rate;
--再建表
CREATE TABLE IF NOT EXISTS online_edu_rpt.rpt_valid_clue_apply_conversion_rate(
    --统计日期
    date_time string COMMENT '标记哪天工作',
    --时间维度
    year_code string COMMENT '年,如2014',
    month_code string COMMENT '月份,如01',
    year_month string COMMENT '年月,如201401',
    day_code string COMMENT '每月第几天',
    year_day string COMMENT '日期主键,如20140224',
    --时间标记
    time_type STRING COMMENT '时间维度标记字段',
    --维度
    origin_type_new string COMMENT '线上线下',
    --指标
    apply_cnt bigint COMMENT '报名人数',
    valid_cnt bigint COMMENT '有效人数',
    valid_clue_apply_conversion_rate DECIMAL(5,2) COMMENT '有效线索报名转换率'
)
COMMENT '每天/每月/每年线上的有效线索报名转换率 = 报名人数 / 有效线索人数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');